package com.c2soft.nepalcmx.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.c2soft.nepalcmx.command.AccountDetailCommand;
import com.c2soft.nepalcmx.command.AccountDetailCommand;
import com.c2soft.nepalcmx.command.BrokerFormCommand;
import com.c2soft.nepalcmx.command.TradeBookCommand;



public class BrokerAccountDetailDAO {
	
	@Autowired
	private DataSource dataSource;
	
	private JdbcTemplate jdbcTemplate;
	
	
	
	public List<AccountDetailCommand> findBrokerAcctDetails(String brokerId) {
		String sql="select * from brokers where broker_Id=? and user_Id=''";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{brokerId},new BeanPropertyRowMapper(AccountDetailCommand.class));
		return accounts;

	}
	
	public List<AccountDetailCommand> findBroker(String brokerId) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		int maxId = jdbcTemplate.queryForInt("select max(id) from account where broker_Id=? and user_ID='' ",brokerId);
		
		
		String sql = "select cashl,marginl,creditamt,debitamt,BALANCED_MARGIN,UTILISED_MARGIN from account where broker_Id=? and user_id='' and id="+maxId;
		
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{brokerId},new BrokerCashFundViewMapper());
		
		return accounts;

	}

	private final class BrokerCashFundViewMapper implements RowMapper<AccountDetailCommand> {

	    public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	AccountDetailCommand acc = new AccountDetailCommand();
	    	acc.setCashl(rs.getString(1));
	    	acc.setMarginl(rs.getString(2));
	    	acc.setCreditamt(rs.getString(3));
	    	acc.setDebitamt(rs.getString(4));
	    	acc.setMargin_b(rs.getString(5));
	    	acc.setMargin_u(rs.getString(6));
	        return acc;
	     
	}
	}
	
	public void updateAccountDetailService(AccountDetailCommand account) {
		String sql = "UPDATE brokers SET BNAME='"+account.getBname()+"',ACCNO='"+account.getAccno()+"',"
				+ "BROKAGE='"+account.getBrokage()+"',CASHL='"+account.getCashl()+"',MARGINL='"+account.getMarginl()+"',"
						+ "CREDITAMT='"+account.getCreditamt()+"',DEBITAMT='"+ account.getDebitamt()+"',IFSC='"+account.getIfsc()
								+ "',BILLNO='"+account.getBillno()+"',CHEQUENO='"+account.getChequeno()+"',BADDRESS='"+account.getBaddress()+
								"' WHERE BROKER_ID='"+ account.getBrokerId()+"' and USER_ID=''";
		
		jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql);
		String sql1="INSERT INTO account(BROKER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,remark) values(?,?,?,?,?,'Account Updated')";
		jdbcTemplate.update(sql1,
				new Object[] { account.getBrokerId(),account.getAccno(),account.getCashl(),account.getMarginl(),account.getMarginl()});

	}
	/*************************************************/
	public List<AccountDetailCommand> findBrokerAccountDetails(String brokerId) {
		jdbcTemplate = new JdbcTemplate(dataSource);
		int maxId = jdbcTemplate.queryForInt("select max(id) from account where broker_Id=? and user_id='' ",brokerId);
		
		String sql = "select CASHL,MARGINL,CREDITAMT,DEBITAMT,BALANCED_MARGIN,UTILISED_MARGIN,ACCNO from account where broker_Id=?  "
				+ "and id="+maxId+" and user_id=''  ";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new Object[]{brokerId},new BrokerAccountMapper());
		return accounts;

	}
	
	private final class BrokerAccountMapper implements RowMapper<AccountDetailCommand> {

	    public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	AccountDetailCommand acc = new AccountDetailCommand();
	    	acc.setCashl(rs.getString(1));
	    	acc.setMarginl(rs.getString(2));
	    	acc.setCreditamt(rs.getString(3));
	    	acc.setDebitamt(rs.getString(4));
	    	acc.setMargin_b(rs.getString(5));
	    	acc.setAccno(rs.getString(7));
	    	acc.setMargin_u(rs.getString(6));
	        return acc;
	    } 
	}
	
	public List<AccountDetailCommand> findExchangeAccountDetails() {
		jdbcTemplate = new JdbcTemplate(dataSource);
		int maxId = jdbcTemplate.queryForInt("select max(id) from account where broker_Id=''  and user_id ='' ");
		String sql = "select CASHL,MARGINL,CREDITAMT,DEBITAMT,BALANCED_MARGIN,UTILISED_MARGIN,ACCNO from account where broker_Id=''   and id="+maxId+" and user_id=''  ";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new BrokerAccountMapper());
		return accounts;

	}

	/**********************************************************/
	public List<AccountDetailCommand> findBrokersAccount() {
		jdbcTemplate = new JdbcTemplate(dataSource);
		String sql="select cashl,MARGINL,BALANCED_MARGIN,UTILISED_MARGIN,BROKER_ID from account where id IN(select max(id) from account where broker_id!='' and user_id ='' GROUP BY BROKER_ID)";
		List<AccountDetailCommand> accounts=jdbcTemplate.query(sql,new UserMapper());
		return accounts;
		
	}
	private final class UserMapper implements RowMapper<AccountDetailCommand> {

	    public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	AccountDetailCommand acc = new AccountDetailCommand();
	    	acc.setCashl(rs.getString(1));
	    	acc.setMarginl(rs.getString(2));
	    	acc.setMargin_b(rs.getString(3));
	    	acc.setMargin_u(rs.getString(4));
	    	acc.setBrokerId(rs.getString(5));
	    	String sql1="SELECT MOBILE,BROKAGE from brokers where broker_id='"+acc.getBrokerId()+"' and user_id='' ";
	    	List contact=jdbcTemplate.query(sql1,
	    			new ContactMapper());
	    	Iterator itr=contact.iterator();
	    	while(itr.hasNext()){
	    		AccountDetailCommand acc1=(AccountDetailCommand)itr.next();
	    		acc.setMobile(acc1.getMobile());
	    		acc.setBrokage(acc1.getBrokage());
	    	}	
	        return acc;   
	}
	}

	    private final class ContactMapper implements RowMapper<AccountDetailCommand> {
	    	public AccountDetailCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	AccountDetailCommand netpos = new AccountDetailCommand();
		    	netpos.setMobile(rs.getString(1));
		    	netpos.setBrokage(rs.getString(2));
		        return netpos;
		    } 
}
	    
	    public void deleteBrokerAccount(String brokerId){
	    	
	    	jdbcTemplate = new JdbcTemplate(dataSource);
	    	String data="select * from brokers where broker_id=? and user_Id=''";
	    	List<BrokerFormCommand> broker=jdbcTemplate.query(data,new Object[]{brokerId},new BeanPropertyRowMapper(BrokerFormCommand.class));
	    	for(BrokerFormCommand b:broker){
	    		String insertData="INSERT INTO users_history(BROKER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,FALSE)";
	    		jdbcTemplate.update(insertData,
	    				new Object[] { b.getbrokerId(),b.getFname(),b.getMname(), b.getLname(),
	    				b.getPassword(), b.getRegno(), b.getTranspwd(),
	    				b.getGender(),b.getPhone(), b.getMobile(), b.getEmailID(),b.getPaddress(),b.getCaddress(), 
	    				b.getPostcode(),b.getCity(),b.getCountry(), b.getIdproof(), b.getIdproofno()});
	    	
	    	}
	    	
	    	String data1="select * from brokers where broker_id=? and user_id!=''";
	    	List<BrokerFormCommand> users=jdbcTemplate.query(data1, new Object[]{brokerId},new BeanPropertyRowMapper(BrokerFormCommand.class));
	    	for(BrokerFormCommand b1:users){
	    		String user=b1.getUserId();
	    		/*String insertData="INSERT INTO users_history(BROKER_ID,USER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,FALSE)";
	    		jdbcTemplate.update(insertData,
	    				new Object[] { b1.getbrokerId(),b1.getUserId(),b1.getFname(),b1.getMname(), b1.getLname(),
	    				b1.getPassword(), b1.getRegno(), b1.getTranspwd(),
	    				b1.getGender(),b1.getPhone(), b1.getMobile(), b1.getEmailID(),b1.getPaddress(),b1.getCaddress(), 
	    				b1.getPostcode(),b1.getCity(),b1.getCountry(), b1.getIdproof(), b1.getIdproofno()});*/
	    	
	    		String User="update brokers set broker_id='' where user_id='"+user+"' and broker_id='"+brokerId+"'";
	    	jdbcTemplate.update(User);
		    		
	    	}
	    	String sql="Delete from users where user_id=?";
	    	jdbcTemplate.update(sql,new Object[]{brokerId});
	    	String sql1="Delete from brokers where broker_id=? and user_Id=''";
	    	jdbcTemplate.update(sql1,new Object[]{brokerId});
	    	String sql2="Delete from ordermgmt where brokeId=? and user_Id=''";
	    	jdbcTemplate.update(sql2,new Object[]{brokerId});
	    	String sql3="Delete from account where broker_id=? and user_Id=''";
	    	jdbcTemplate.update(sql3,new Object[]{brokerId});	
	    	String sql4="Delete from user_roles where user_id=?";
	    	jdbcTemplate.update(sql4,new Object[]{brokerId});
	    	
	    }
	    
public void deleteUserAccount(String userId){
	jdbcTemplate = new JdbcTemplate(dataSource);
	String data="select * from brokers where user_id=?";
	List<BrokerFormCommand> user=jdbcTemplate.query(data,new Object[]{userId},new BeanPropertyRowMapper(BrokerFormCommand.class));
	for(BrokerFormCommand u:user){
		String insertData="INSERT INTO users_history(BROKER_ID,USER_ID, FNAME,MNAME,LNAME,PASSWORD,REGNO,TRANSPWD,GENDER,PHONE,MOBILE,EMAILID,PADDRESS,CADDRESS,POSTCODE,CITY,COUNTRY,IDPROOF,IDPROOFNO, ENABLED) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,FALSE)";
		jdbcTemplate.update(insertData,
				new Object[] { u.getbrokerId(),u.getUserId(),u.getFname(),u.getMname(), u.getLname(),
				u.getPassword(), u.getRegno(), u.getTranspwd(),
				u.getGender(),u.getPhone(), u.getMobile(), u.getEmailID(),u.getPaddress(),u.getCaddress(), u.getPostcode(),u.getCity(),
				u.getCountry(), u.getIdproof(), u.getIdproofno()});
	}
	String sql="Delete from users where user_id=?";
	jdbcTemplate.update(sql,new Object[]{userId});
	String sql1="Delete from brokers where user_id=?";
	jdbcTemplate.update(sql1,new Object[]{userId});
	String sql2="Delete from ordermgmt where user_id=?";
	jdbcTemplate.update(sql2,new Object[]{userId});
	String sql3="Delete from account where user_id=?";
	jdbcTemplate.update(sql3,new Object[]{userId});
	String sql4="Delete from user_roles where user_id=?";
	jdbcTemplate.update(sql4,new Object[]{userId});
	    }
	    
	   
}
